﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Module;
using System.Data.SqlClient;
using Qmaker.DAL;
using System.Data;
using EMPower.Utility;

namespace DLL
{
    public class DPaperTag
    {
        #region SQL语句
        private const string SQL_INSERT = "insert into PaperTag(PaperTagId,PaperId,TagId,DateCreated) values (@PaperTagId,@PaperId,@TagId,@DateCreated)";

        private const string SQL_UPDATE = "update PaperTag set PaperId=@PaperId,TagId=@TagId,DateCreated=@DateCreated  where PaperTagId=@PaperTagId";

        private const string SQL_DELETE_PAPERID_TAGID = "delete from PaperTag where PaperId=@PaperId and TagId=@TagId";

        private const string SQL_DELETE_PAPERID = "delete from PaperTag where PaperId=@PaperId";

        private const string SQL_DELETE_ID = "delete from PaperTag where PaperTagId=@PaperTagId";

        #endregion

        #region 公共接口
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="paperTagInfo"></param>
        /// <returns></returns>
        public bool Insert(PaperTagInfo paperTagInfo)
        {
            if (paperTagInfo == null)
                throw new ArgumentNullException("paperTagInfo");

            SqlParameter[] parms = GetInsertParms(paperTagInfo);
            parms = SetInsertParmsCheck(parms);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        Convert.ToInt32(SqlHelper.ExecuteScalar(trans, CommandType.Text, SQL_INSERT, parms));
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="paperInfo"></param>
        /// <returns></returns>
        public bool Update(PaperTagInfo paperTagInfo)
        {
            if (paperTagInfo == null || paperTagInfo.PaperTagId.ToString().Length != 18)
                throw new ArgumentNullException("paperTagInfo");

            SqlParameter[] parms = GetInsertParms(paperTagInfo);
            parms = SetInsertParmsCheck(parms);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE, parms);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 根据PagerId和TagId删除
        /// </summary>
        /// <param name="paperId"></param>
        /// <param name="tagId"></param>
        /// <returns></returns>
        public int Delete(long paperId,long tagId)
        {
            SqlParameter[] parms = {
                    new SqlParameter("@PaperId",SqlDbType.BigInt),
                    new SqlParameter("@TagId",SqlDbType.BigInt)
                };
            parms[0].Value = paperId;
            parms[1].Value = tagId;
            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, SQL_DELETE_PAPERID_TAGID, parms);

        }

        /// <summary>
        /// 根据PagerId删除
        /// </summary>
        /// <param name="paperId"></param>
        /// <returns></returns>
        public int Delete(long paperId)
        {
            SqlParameter[] parms = {
                    new SqlParameter("@PaperId",SqlDbType.BigInt)
                };
            parms[0].Value = paperId;
            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, SQL_DELETE_PAPERID, parms);

        }

        /// <summary>
        /// 根据主键删除
        /// </summary>
        /// <param name="paperTagId"></param>
        /// <returns></returns>
        public int DeleteById(long paperTagId)
        {
            SqlParameter[] parms = {
                    new SqlParameter("@PaperTagId",SqlDbType.BigInt)
                };
            parms[0].Value = paperTagId;


            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, SQL_DELETE_ID, parms);

        }


        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="paperTagInfos"></param>
        /// <returns></returns>
        public bool Insert(List<PaperTagInfo> paperTagInfos)
        {
            if (paperTagInfos == null || paperTagInfos.Count == 0)
                return false;
            //DataTable dt = new DataTable();
            ////PaperTagId,PaperId,TagId,DateCreated
            //dt.Columns.Add("PaperTagId", typeof(long));
            //dt.Columns.Add("PaperId", typeof(long));
            //dt.Columns.Add("TagId", typeof(long));
            //dt.Columns.Add("DateCreated", typeof(DateTime));


            //foreach (PaperTagInfo l in paperTagInfos)
            //{
            //    DataRow dr = dt.NewRow();
            //    dr["PaperTagId"] = l.PaperTagId;

            //    dr["PaperId"] = l.PaperId;
            //    dr["TagId"] = l.TagId;
            //    dr["DateCreated"] = DateTime.Now;
            //    dt.Rows.Add(dr);
            //}

           DataTable dt = Common.ListToDataTable(paperTagInfos);
            using (SqlConnection conn = new SqlConnection(Qmaker.DAL.SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))
                    {
                        bulkCopy.BatchSize = 2000;
                        bulkCopy.BulkCopyTimeout = 30;
                        bulkCopy.DestinationTableName = "PaperTag";
                        try
                        {
                            foreach (DataColumn col in dt.Columns)
                            {
                                bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                            }
                            bulkCopy.WriteToServer(dt);
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            throw ex;
                        }
                        finally
                        {
                            conn.Close();
                        }
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 事物中批量插入
        /// </summary>
        /// <param name="paperTagInfos"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public bool Insert(List<PaperTagInfo> paperTagInfos, SqlTransaction trans)
        {
            if (paperTagInfos == null || paperTagInfos.Count == 0)
                return false;           

            DataTable dt = Common.ListToDataTable(paperTagInfos);

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(trans.Connection, SqlBulkCopyOptions.Default, trans))
            {
                bulkCopy.BatchSize = 2000;
                bulkCopy.BulkCopyTimeout = 30;
                bulkCopy.DestinationTableName = "PaperTag";
                try
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    }
                    bulkCopy.WriteToServer(dt);

                }
                catch (Exception ex)
                {
                    throw ex;
                }

            }
            return true;
        }

        #endregion


        #region 私有接口
        private SqlParameter[] SetInsertParmsCheck(SqlParameter[] parms)
        {
            for (int i = 0; i < parms.Length; i++)
            {
                if (parms[i].Value == null)
                {
                    parms[i].Value = DBNull.Value;
                }
                if (parms[i].DbType == DbType.DateTime && parms[i].Value.Equals(DateTime.MinValue))
                {
                    parms[i].Value = DBNull.Value;
                }
            }
            return parms;
        }

        private SqlParameter[] GetInsertParms(PaperTagInfo model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@PaperTagId", SqlDbType.BigInt,8),
					new SqlParameter("@PaperId", SqlDbType.BigInt,8),
					new SqlParameter("@TagId", SqlDbType.BigInt,8),
					new SqlParameter("@DateCreated", SqlDbType.DateTime)};
            parameters[0].Value = model.PaperTagId;
            parameters[1].Value = model.PaperId;
            parameters[2].Value = model.TagId;
            parameters[3].Value = model.DateCreated;

            return parameters;
        }

        #endregion

    }
}
